#!/bin/bash

# =====================================================
# 物业管理模块数据库表设计SQL兼容性测试脚本
# 作者: Wu.Liang
# 创建时间: 2025-01-21
# 说明: 测试修复版SQL文件的MySQL 5.7兼容性
# =====================================================

echo "====================================================="
echo "物业管理模块数据库表设计SQL兼容性测试"
echo "====================================================="

# 设置颜色
RED='\033[0;31m'
GREEN='\033[0;32m'
YELLOW='\033[1;33m'
NC='\033[0m' # No Color

# 检查MySQL版本
echo -e "${YELLOW}检查MySQL版本...${NC}"
mysql_version=$(mysql --version 2>/dev/null | grep -oP 'Distrib \K[0-9]+\.[0-9]+' | head -1)

if [ -z "$mysql_version" ]; then
    echo -e "${RED}错误: 未检测到MySQL或无法获取版本信息${NC}"
    exit 1
fi

echo -e "${GREEN}检测到MySQL版本: $mysql_version${NC}"

# 检查是否支持DROP INDEX IF EXISTS语法
if [[ $(echo "$mysql_version >= 8.0" | bc -l 2>/dev/null) -eq 1 ]]; then
    echo -e "${GREEN}MySQL版本支持DROP INDEX IF EXISTS语法${NC}"
    supports_drop_if_exists=true
else
    echo -e "${YELLOW}MySQL版本不支持DROP INDEX IF EXISTS语法，需要使用修复版SQL${NC}"
    supports_drop_if_exists=false
fi

# 检查SQL文件
echo -e "\n${YELLOW}检查SQL文件...${NC}"

original_sql="数据库表设计SQL.sql"
fixed_sql="数据库表设计SQL_修复版.sql"

if [ -f "$original_sql" ]; then
    echo -e "${GREEN}找到原始SQL文件: $original_sql${NC}"
    
    # 检查是否包含不兼容语法
    if grep -q "DROP INDEX IF EXISTS" "$original_sql"; then
        echo -e "${RED}原始SQL文件包含不兼容的DROP INDEX IF EXISTS语法${NC}"
        original_compatible=false
    else
        echo -e "${GREEN}原始SQL文件语法兼容${NC}"
        original_compatible=true
    fi
else
    echo -e "${YELLOW}未找到原始SQL文件${NC}"
    original_compatible=false
fi

if [ -f "$fixed_sql" ]; then
    echo -e "${GREEN}找到修复版SQL文件: $fixed_sql${NC}"
    
    # 检查是否包含不兼容语法
    if grep -q "DROP INDEX IF EXISTS" "$fixed_sql"; then
        echo -e "${RED}修复版SQL文件仍包含不兼容语法${NC}"
        fixed_compatible=false
    else
        echo -e "${GREEN}修复版SQL文件语法兼容${NC}"
        fixed_compatible=true
    fi
    
    # 检查是否包含存储过程
    if grep -q "CREATE PROCEDURE CreateIndexIfNotExists" "$fixed_sql"; then
        echo -e "${GREEN}修复版SQL文件包含索引创建存储过程${NC}"
        has_procedure=true
    else
        echo -e "${YELLOW}修复版SQL文件未包含索引创建存储过程${NC}"
        has_procedure=false
    fi
else
    echo -e "${RED}未找到修复版SQL文件${NC}"
    fixed_compatible=false
    has_procedure=false
fi

# 统计表数量
echo -e "\n${YELLOW}统计表结构...${NC}"

if [ -f "$fixed_sql" ]; then
    table_count=$(grep -c "CREATE TABLE IF NOT EXISTS" "$fixed_sql")
    view_count=$(grep -c "CREATE OR REPLACE VIEW" "$fixed_sql")
    trigger_count=$(grep -c "CREATE TRIGGER" "$fixed_sql")
    procedure_count=$(grep -c "CREATE PROCEDURE" "$fixed_sql")
    
    echo -e "${GREEN}修复版SQL文件包含:${NC}"
    echo -e "  - 表: $table_count 个"
    echo -e "  - 视图: $view_count 个"
    echo -e "  - 触发器: $trigger_count 个"
    echo -e "  - 存储过程: $procedure_count 个"
fi

# 检查编码
echo -e "\n${YELLOW}检查文件编码...${NC}"

if [ -f "$fixed_sql" ]; then
    file_encoding=$(file -bi "$fixed_sql" | grep -oP 'charset=\K[^;]+')
    if [ "$file_encoding" = "utf-8" ]; then
        echo -e "${GREEN}修复版SQL文件编码正确: UTF-8${NC}"
    else
        echo -e "${YELLOW}修复版SQL文件编码: $file_encoding${NC}"
    fi
fi

# 生成测试报告
echo -e "\n====================================================="
echo -e "兼容性测试报告"
echo -e "====================================================="

echo -e "MySQL版本: $mysql_version"
echo -e "支持DROP INDEX IF EXISTS: $supports_drop_if_exists"

if [ "$original_compatible" = true ]; then
    echo -e "原始SQL文件兼容性: ${GREEN}✅ 兼容${NC}"
else
    echo -e "原始SQL文件兼容性: ${RED}❌ 不兼容${NC}"
fi

if [ "$fixed_compatible" = true ]; then
    echo -e "修复版SQL文件兼容性: ${GREEN}✅ 兼容${NC}"
else
    echo -e "修复版SQL文件兼容性: ${RED}❌ 不兼容${NC}"
fi

if [ "$has_procedure" = true ]; then
    echo -e "索引创建存储过程: ${GREEN}✅ 已包含${NC}"
else
    echo -e "索引创建存储过程: ${RED}❌ 未包含${NC}"
fi

# 最终建议
echo -e "\n====================================================="
echo -e "执行建议"
echo -e "====================================================="

if [ "$fixed_compatible" = true ] && [ "$has_procedure" = true ]; then
    echo -e "${GREEN}✅ 建议使用修复版SQL文件执行${NC}"
    echo -e "文件路径: $fixed_sql"
    echo -e "执行命令: mysql -u [用户名] -p [数据库名] < $fixed_sql"
else
    echo -e "${RED}❌ 修复版SQL文件存在问题，需要进一步检查${NC}"
fi

echo -e "\n====================================================="
echo -e "测试完成"
echo -e "=====================================================" 